扫一扫
分享文章到微信
扫一扫
关注官方公众号
至顶头条
来源:IT专家网 2011年3月5日
问题:
当我们浏览SQL Server 2008的新特性的时候,我们发现一个很有趣的特性叫做表值参数(Table-Valued Parameter)。你能否给我们详细介绍一下我们可以如何利用这个新特性吗?
专家解答:
表值参数确实是SQL Server 2008的一个新特性。顾名思义,表值参数表示你可以把一个表类型作为参数传递到函数或存储过程里。更高级的功能方面,表值参数的功能可以允许你向被声明为T-SQL变量的表中导入数据,然后把该表作为一个参数传递到存储过程或函数中去。表值参数的优点在于你可以向存储过程或函数发送多行数据,而无需向以前那样必须声明多个参数或者使用XML参数类型来处理多行数据。据说,表值参数可以处理多达1000行数据。
我们在这里将会介绍表值参数,并举一些简单的编码例子来演示如何通过使用表值参数来完成以下任务:
创建可以作为表值参数传递到函数或存储过程的表类型
创建使用表值参数的存储过程
声明表类型,向该表导入数据,并把它传递到存储过程里
在数据仓库应用程序里的数据加载过程,我们一般会在维度处理过程中把源系统键对应到代理键;然后使用代理键来识别数据仓库中的维度行。这样对维度行进行的每一个改动都会存储在一行带有新代理键的新行中,我们就可以保存维度行的完整历史记录。当对维度行进行改动或添加新行时,我们只需要给源系统键添加一个新的代理键并在维度表里插入新的一行就可以了。在处理事实行(fact rows)时,我们查找代理键并将其存储在事实表中。查询通过代理键连接事实表和维度表。由于多个事实表通常会索引至同一个维度(例如Customer),代理键查找功能可以给我们提供一个使用表值参数的很好的例子。我们可以在存储过程中执行一次代理键查找,然后在多个事实表的数据载入过程中调用这个代理键查找。
除了简单的查找源系统键的代理键之外,还有一种情况可利用表值参数,即事实表含有一个不存在于维度表的源系统键。在这种情况下,我们则想要在维度里创建一个推断成员(Inferred member),也就是说,创建一个新的代理键并把它添加到维度里,在我们从源系统里获得真正的维度行之后再把它替换更新掉。
下面的演示编码只在SQL Server 2008的二月份社区测试试用版中进行过测试。
新建一个表类型
为了把表作为一个参数传递到存储过程或函数中,你首先要创建一个TABLE TYPE,如下所示:
以下是引用片段:
CREATE TYPE SourceKeyList AS TABLE (
SourceKey NVARCHAR(50)
)
GO
T-SQL编码跟新建一个普通的表的编码非常相似。你可以查询当前数据库的sys.types来确定已创建的任何表类型:
以下是引用片段:
SELECT name, system_type_id, user_type_id
FROM sys.types
WHERE is_table_type = 1
新建一个带有表值参数的存储过程
我们要创建一个存储过程,该存储过程执行代理键查找命令,如果源键不存在则添加一个推理成员。首先我们需要新建一个维度表样例:
以下是引用片段:
CREATE TABLE dbo.dim_Customer (
sk_Customer INT IDENTITY NOT NULL,
CustomerSourceKey NVARCHAR(50) NOT NULL,
CustomerName NVARCHAR(50) NOT NULL,
InferredMember BIT NOT NULL
)
代理键是整数类型,我们使用IDENTITY属性来自动分配插入行的序列号。当我们插入一行源键不存在的行时,InferredMember列设置为1。当我们在维度处理过程中从源系统中获得该行后,它将替换掉推理成员行,而InferredMember列的值会变成0。
现在我们来创建一个接受新建表类型作为参数并执行代理键查找和推理处理的存储过程:
以下是引用片段:
CREATE PROCEDURE dbo.stp_GetCustomerSK
@source_key_list SourceKeyList READONLY
AS
BEGIN
INSERT INTO dbo.dim_Customer(
CustomerSourceKey, CustomerName, InferredMember
)
SELECT SourceKey, N'INFERRED', 1
FROM @source_key_list k
LEFT JOIN dbo.dim_Customer c ON c.CustomerSourceKey = k.SourceKey
WHERE sk_Customer IS NULL
SELECT sk_Customer, CustomerSourceKey
FROM dbo.dim_Customer c
JOIN @source_key_list k ON k.SourceKey = c.CustomerSourceKey
END
GO
表值参数必须被声明为READONLY。你不能对表值参数执行任何DML(即插入、更新、删除)。你职能在SELECT语句里引用它。存储过程把表值参数与客户维度连接起来,查找出任何还不存在的源键,然后插入这些键。然后存储过程再把表值参数与客户维度连接来返回含有源键及其对应代理键的结果集。
你可以通过查询sys.parameters来查看任何被声明为READONLY的参数:
以下是引用片段:
SELECT object_id, name FROM sys.parameters
WHERE is_readonly = 1
GO
声明表值参数,导入数据,并把它传递到存储过程里
你声明一个表类型的T-SQL变量,使用INSERT插入语句向该表导入数据:
以下是引用片段:
DECLARE @source_key_list SourceKeyList
INSERT INTO @source_key_list
SELECT 'CustomerID_001' UNION ALL
SELECT 'CustomerID_002' UNION ALL
SELECT 'CustomerID_003'
EXEC dbo.stp_GetCustomerSK @source_key_list
GO
为了演示的目的,上面的SELECt语句只是硬编码一些值来插入;你通常可以从你的源系统表里执行SELECT DISTINCT命令来获得你希望对其执行代理键查找功能的源系统键的列表。上面脚本返回的结果应该如下图所示:
返回结果显示了每一个源键的代理键。
濠电姷鏁告慨鐑姐€傛禒瀣劦妞ゆ巻鍋撻柛鐔锋健閸┾偓妞ゆ巻鍋撶紓宥咃躬楠炲啫螣鐠囪尙绐為梺褰掑亰閸撴盯鎮惧ú顏呪拺闂傚牊鍗曢崼銉ョ柧婵犲﹤瀚崣蹇旂節婵犲倻澧涢柛瀣ㄥ妽閵囧嫰寮介妸褋鈧帡鏌熼挊澶婃殻闁哄瞼鍠栭幃婊堝煛閸屾稓褰嬮柣搴ゎ潐濞叉ê鐣濈粙璺ㄦ殾闁割偅娲栭悡娑㈡煕鐏炲墽鐭嬫繛鍫熸倐濮婄粯鎷呯粵瀣異闂佹悶鍔嬮崡鍐茬暦閵忋倕鍐€妞ゆ劑鍎卞皬闂備焦瀵х粙鎴犫偓姘煎弮瀹曚即宕卞Ο闀愮盎闂侀潧鐗嗛幊搴㈡叏椤掆偓閳规垿鍩ラ崱妞剧凹濠电姰鍨洪敋閾荤偞淇婇妶鍛櫤闁稿鍊圭换娑㈠幢濡纰嶉柣搴㈣壘椤︾敻寮诲鍫闂佸憡鎸鹃崰搴敋閿濆鏁嗗〒姘功閻绻涢幘鏉戠劰闁稿鎹囬弻锝呪槈濞嗘劕纾抽梺鍝勬湰缁嬫垿鍩為幋锕€宸濇い鏇炴噺閳诲﹦绱撻崒娆戝妽妞ゃ劌鎳橀幆宀勫磼閻愰潧绁﹂柟鍏肩暘閸斿矂鎮為崹顐犱簻闁圭儤鍨甸鈺呮倵濮橆剦妲归柕鍥у瀵粙濡歌閸c儳绱撴担绛嬪殭婵☆偅绻堝濠氭偄绾拌鲸鏅i悷婊冪Ч閹﹢鎳犻鍌滐紲闁哄鐗勯崝搴g不閻愮儤鐓涢悘鐐跺Г閸犳﹢鏌℃担鐟板鐎规洜鍠栭、姗€鎮╅搹顐ら拻闂傚倷娴囧畷鍨叏閹惰姤鈷旂€广儱顦崹鍌炴煢濡尨绱氶柨婵嗩槸缁€瀣亜閺嶃劎鈽夋繛鍫熺矒濮婅櫣娑甸崨顔俱€愬銈庡亝濞茬喖宕洪埀顒併亜閹哄棗浜鹃梺鎸庢穿婵″洤危閹版澘绫嶉柛顐g箘椤撴椽姊虹紒妯哄鐎殿噮鍓欒灃闁告侗鍠氶崢鎼佹⒑閸撴彃浜介柛瀣閹﹢鏁冮崒娑氬幈闁诲函缍嗛崑鍡樻櫠椤掑倻纾奸柛灞剧☉缁椦囨煙閻熸澘顏柟鐓庢贡閹叉挳宕熼棃娑欐珡闂傚倸鍊风粈渚€骞栭銈傚亾濮樺崬鍘寸€规洖缍婇弻鍡楊吋閸涱垽绱遍柣搴$畭閸庨亶藝娴兼潙纾跨€广儱顦伴悡鏇㈡煛閸ャ儱濡煎褜鍨伴湁闁绘ǹ绉鍫熺畳闂備焦瀵х换鍌毼涘Δ鍛厺闁哄洢鍨洪悡鍐喐濠婂牆绀堟慨妯挎硾閽冪喖鏌曟繛褍瀚烽崑銊╂⒑缂佹ê濮囨い鏇ㄥ弮閸┿垽寮撮姀鈥斥偓鐢告煥濠靛棗鈧懓鈻嶉崶銊d簻闊洦绋愰幉楣冩煛鐏炵偓绀嬬€规洟浜堕、姗€鎮㈡總澶夌处